#include "sql.h"

QSqlDatabase db;
QSqlQuery query;

bool openDB()
{
    // 打开数据库
    db = QSqlDatabase::addDatabase("QSQLITE","wj");
    db.setDatabaseName("../data/db/server.db");

    db.setUserName("admin");
    db.setPassword("admin");
    if(!db.open())
    {
        qDebug()<<"open error";
        return false;
    }
    //QSqlQuery query(db);
    query = QSqlQuery(db);
    return true;
}

bool insertUser(QString username, QString passwd)
{
    QString sql = "insert into user (username, password) values(?,?)";
    query.prepare(sql);
    query.addBindValue(username);
    query.addBindValue(passwd);
    if(!query.exec())
    {
        qDebug() << query.lastError();
        return false;
    }
    return true;
}

bool insertBlog(QString user, QString title, QString overview, QString time, QString stamp, QString praise, \
                QString collection, QString comment, QString type, QString pay, QString pix)
{
    QString sql = "insert into blog (user, title, overview, time, stamp, praise, collection, comment, type, pay, pix) \
            values(?,?,?,?,?,?,?,?,?,?,?)";
            query.prepare(sql);
    query.addBindValue(user);
    query.addBindValue(title);
    query.addBindValue(overview);
    query.addBindValue(time);
    query.addBindValue(stamp);
    query.addBindValue(praise);
    query.addBindValue(collection);
    query.addBindValue(comment);
    query.addBindValue(type);
    query.addBindValue(pay);
    query.addBindValue(pix);

    if(!query.exec())
    {
        qDebug() << "insert blog" << query.lastError();
        return false;
    }
    return true;
}

bool insertRes(QString user, QString title, QString name, QString size, QString time, QString stamp, QString type, QString pay, QString counts)
{
    QString sql = "insert into res (user, title, name, size, time, stamp, type, pay, dcounts) values(?,?,?,?,?,?,?,?,?)";
    query.prepare(sql);
    query.addBindValue(user);
    query.addBindValue(title);
    query.addBindValue(name);
    query.addBindValue(size);
    query.addBindValue(time);
    query.addBindValue(stamp);
    query.addBindValue(type);
    query.addBindValue(pay);
    query.addBindValue(counts);
    if(!query.exec())
    {
        qDebug() << query.lastError();
        return false;
    }
    return true;
}

bool insertPraise(QString blogName, QString blogUser, QString praiseUser)
{
    QString sql = "insert into praise (blog_name, blog_user, praise_user) values(?,?,?)";
    query.prepare(sql);
    query.addBindValue(blogName);
    query.addBindValue(blogUser);
    query.addBindValue(praiseUser);
    if(!query.exec())
    {
        qDebug() << query.lastError();
        return false;
    }
    return true;
}

bool insertCollection(QString blogName, QString blogUser, QString collectionUser)
{
    QString sql = "insert into collection (blog_name, blog_user, collection_user) values(?,?,?)";
    query.prepare(sql);
    query.addBindValue(blogName);
    query.addBindValue(blogUser);
    query.addBindValue(collectionUser);
    if(!query.exec())
    {
        qDebug() << query.lastError();
        return false;
    }
    return true;
}

bool insertComment(QString blogUser, QString blogName, QString commentUser, QString commentTime, QString commentContent)
{
    QString sql = "insert into comment (blog_user, blog_name, comment_user, comment_time, comment_content) values(?,?,?,?,?)";
    query.prepare(sql);
    query.addBindValue(blogUser);
    query.addBindValue(blogName);
    query.addBindValue(commentUser);
    query.addBindValue(commentTime);
    query.addBindValue(commentContent);
    if(!query.exec())
    {
        qDebug() << query.lastError();
        return false;
    }
    return true;
}

QMap<QString, QString> selectUser(QString username)
{
    QMap<QString, QString> mapinfo;
    QString sql = "select * from user where username=?";
    query.prepare(sql);
    query.addBindValue(username);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        if(query.next())
        {
            mapinfo.insert("username", query.value(1).toString());
            mapinfo.insert("password", query.value(2).toString());
            mapinfo.insert("head", query.value(3).toString());
            mapinfo.insert("isLogin", query.value(4).toString());
        }
    }
    return mapinfo;
}

QMap<QString, QString> selectBlog(QString user, QString stamp)
{
    QMap<QString, QString> map;

    QString sql = "select * from blog where user=? AND stamp=?";
    query.prepare(sql);
    query.addBindValue(user);
    query.addBindValue(stamp);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        if(query.next())
        {
            map.insert("user", query.value(1).toString());
            map.insert("title", query.value(2).toString());
            map.insert("overview", query.value(3).toString());
            map.insert("time", query.value(4).toString());
            map.insert("stamp", query.value(5).toString());
            map.insert("praise", query.value(6).toString());
            map.insert("collection", query.value(7).toString());
            map.insert("comment", query.value(8).toString());
            map.insert("type", query.value(9).toString());
            map.insert("pay", query.value(10).toString());
            map.insert("pix", query.value(11).toString());
        }
    }
    return  map;
}

QMap<int, QMap<QString, QString>> selectBlogLimit(QString key, QString value, QString limit, QString offset)
{
    QMap<int, QMap<QString, QString>> map;

    QString sql = "select * from blog where %1=? limit %2 offset %3";

    QString sqlSelect = sql.arg(key, limit, offset);
    query.prepare(sqlSelect);
    query.addBindValue(value);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        int i=0;
        while(query.next())
        {
            QMap<QString, QString> tmp;
            tmp.insert("id", QString::number(query.value(0).toInt()));
            tmp.insert("user", query.value(1).toString());
            tmp.insert("title", query.value(2).toString());
            tmp.insert("overview", query.value(3).toString());
            tmp.insert("time", query.value(4).toString());
            tmp.insert("stamp", query.value(5).toString());
            tmp.insert("praise", query.value(6).toString());
            tmp.insert("collection", query.value(7).toString());
            tmp.insert("comment", query.value(8).toString());
            tmp.insert("type", query.value(9).toString());
            tmp.insert("pay", query.value(10).toString());
            tmp.insert("pix", query.value(11).toString());
            map.insert(i++, tmp);
        }
    }
    return  map;
}

QMap<int, QMap<QString, QString>> selectRes(QString key, QString value)
{
    QMap<int, QMap<QString, QString>> map;

    QString sql = "select * from res where %1=?";
    QString sqlSelect = sql.arg(key);
    query.prepare(sqlSelect);
    query.addBindValue(value);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        int i=0;
        while(query.next())
        {
            QMap<QString, QString> tmp;
            tmp.insert("name", query.value(1).toString());
            tmp.insert("title", query.value(2).toString());
            tmp.insert("user", query.value(3).toString());
            tmp.insert("time", query.value(4).toString());
            tmp.insert("dcounts", query.value(5).toString());
            map.insert(query.value(0).toInt(), tmp);
            i++;
            if(i>=10)
                break;
        }
    }
    return  map;
}

QMap<int, QMap<QString, QString>> selectResLimit(QString key, QString value, QString limit, QString offset)
{
    QMap<int, QMap<QString, QString>> map;

    QString sql = "select * from res where %1=? limit %2 offset %3";
    QString sqlSelect = sql.arg(key, limit, offset);
    query.prepare(sqlSelect);
    query.addBindValue(value);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        int i=0;
        while(query.next())
        {
            QMap<QString, QString> tmp;
            tmp.insert("user", query.value(1).toString());
            tmp.insert("title", query.value(2).toString());
            tmp.insert("name", query.value(3).toString());
            tmp.insert("size", query.value(4).toString());
            tmp.insert("time", query.value(5).toString());
            tmp.insert("stamp", query.value(6).toString());
            tmp.insert("type", query.value(7).toString());
            tmp.insert("pay", query.value(8).toString());
            tmp.insert("dcounts", query.value(9).toString());
            map.insert(i++, tmp);
        }
    }
    return  map;
}

bool selectPraise(QString value1, QString value2, QString value3)
{
    QString sql = "select * from praise where blog_name=? AND blog_user=? AND praise_user=?";
    query.prepare(sql);
    query.addBindValue(value1);
    query.addBindValue(value2);
    query.addBindValue(value3);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        if(query.next())
        {
            return true;
        }
    }
    return  false;
}

bool selectCollection(QString value1, QString value2, QString value3)
{
    QString sql = "select * from collection where blog_name=? AND blog_user=? AND collection_user=?";
    query.prepare(sql);
    query.addBindValue(value1);
    query.addBindValue(value2);
    query.addBindValue(value3);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        if(query.next())
        {
            return true;
        }
    }
    return false;
}

QMap<int, QMap<QString, QString>> selectComment(QString key, QString value)
{
    QMap<int, QMap<QString, QString>> map;

    QString sql = "select * from comment where %1=?";
    QString sqlSelect = sql.arg(key);
    query.prepare(sqlSelect);
    query.addBindValue(value);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        while(query.next())
        {
            QMap<QString, QString> tmp;
            tmp.insert("blog_name", query.value(1).toString());
            tmp.insert("blog_user", query.value(2).toString());
            tmp.insert("comment_user", query.value(3).toString());
            tmp.insert("comment_time", query.value(4).toString());
            tmp.insert("comment_content", query.value(5).toString());
            map.insert(query.value(0).toInt(), tmp);
        }
    }
    return  map;
}

QMap<int, QMap<QString, QString>> selectCommentLimit(QString key, QString value, QString limit, QString offset)
{
    QMap<int, QMap<QString, QString>> map;

    QString sql = "select * from comment where %1=? limit %2 offset %3";

    QString sqlSelect = sql.arg(key, limit, offset);
    query.prepare(sqlSelect);
    query.addBindValue(value);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        int i=0;
        while(query.next())
        {
            QMap<QString, QString> tmp;
            tmp.insert("id", QString::number(query.value(0).toInt()));
            tmp.insert("blog_user", query.value(1).toString());
            tmp.insert("blog_name", query.value(2).toString());
            tmp.insert("comment_user", query.value(3).toString());
            tmp.insert("comment_time", query.value(4).toString());
            tmp.insert("comment_content", query.value(5).toString());
            map.insert(i++, tmp);
        }
    }
    return  map;
}

bool deleteRecord(QString tableName, QString key, QString value)
{
    QString sql = "delete from %1 where %2=?";
    QString delSelect = sql.arg(tableName, key);
    query.prepare(sql);
    query.addBindValue(value);
    if(!query.exec())
    {
        qDebug() << query.lastError();
        return false;
    }
    return true;
}

bool deletePraise(QString blog_name, QString blog_user, QString praise_user)
{
    QString sql = "delete from praise where blog_name=? AND blog_user=? AND praise_user=?";
    query.prepare(sql);
    query.addBindValue(blog_name);
    query.addBindValue(blog_user);
    query.addBindValue(praise_user);
    if(!query.exec())
    {
        qDebug() << query.lastError();
        return false;
    }
    return true;
}

bool deleteCollection(QString blog_name, QString blog_user, QString collection_user)
{
    QString sql = "delete from collection where blog_name=? AND blog_user=? AND collection_user=?";
    query.prepare(sql);
    query.addBindValue(blog_name);
    query.addBindValue(blog_user);
    query.addBindValue(collection_user);
    if(!query.exec())
    {
        qDebug() << query.lastError();
        return false;
    }
    return true;
}

void updateUser(QString user, QString isLogin)
{
    QString sql = "update user set isLogin=? where username=?";
    query.prepare(sql);
    query.addBindValue(isLogin);
    query.addBindValue(user);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
}

bool updateBlogPraise(QString user, QString stamp, QString praise)
{
    QString sql = "update blog set praise=? where user=? AND stamp=?";
    query.prepare(sql);
    query.addBindValue(praise);
    query.addBindValue(user);
    query.addBindValue(stamp);
    if(!query.exec())
    {
        qDebug() << query.lastError();
        return false;
    }
    return true;
}

bool updateBlogCollection(QString user, QString stamp, QString collection)
{
    QString sql = "update blog set collection=? where user=? AND stamp=?";
    query.prepare(sql);
    query.addBindValue(collection);
    query.addBindValue(user);
    query.addBindValue(stamp);
    if(!query.exec())
    {
        qDebug() << query.lastError();
        return false;
    }
    return true;
}

bool updateBlogComment(QString user, QString stamp, QString comment)
{
    QString sql = "update blog set comment=? where user=? AND stamp=?";
    query.prepare(sql);
    query.addBindValue(comment);
    query.addBindValue(user);
    query.addBindValue(stamp);
    if(!query.exec())
    {
        qDebug() << query.lastError();
        return false;
    }
    return true;
}

bool updatePraise(QString username, QString praise)
{
    QString sql = "update blog set praise=:praise where name=:name";
    query.prepare(sql);
    query.bindValue(":praise", praise);
    query.bindValue(":name", username);
    if(!query.exec())
    {
        qDebug() << query.lastError();
        return false;
    }
    return true;
}

QList<QString> countInfo(QString name)
{
    QList<QString> info;
    int praiseNum=0, collectionNum=0, commentNum=0;
    QString sql = "select * from praise where blog_user=?";
    query.prepare(sql);
    query.addBindValue(name);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        while(query.next())
        {
            praiseNum++;
        }
    }

    sql = "select * from collection where blog_user=?";
    query.prepare(sql);
    query.addBindValue(name);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        while(query.next())
        {
            collectionNum++;
        }
    }

    sql = "select * from comment where blog_user=?";
    query.prepare(sql);
    query.addBindValue(name);
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        while(query.next())
        {
            commentNum++;
        }
    }
    info << QString::number(praiseNum) << QString::number(collectionNum) << QString::number(commentNum);
    return info;
}

QMap<int, QMap<QString, QString>> selectCommentByUser(QString user)
{
    QString sql = "select * from comment where blog_user=?";
    query.prepare(sql);
    query.addBindValue(user);

    QMap<int, QMap<QString, QString>> map;
    if(!query.exec())
    {
        qDebug() << query.lastError();
    }
    else
    {
        int i=0;
        while(query.next())
        {
            QMap<QString, QString> tmp;
            tmp.insert("id", QString::number(query.value(0).toInt()));
            tmp.insert("blog_user", query.value(1).toString());
            tmp.insert("blog_name", query.value(2).toString());
            tmp.insert("comment_user", query.value(3).toString());
            tmp.insert("comment_time", query.value(4).toString());
            tmp.insert("comment_content", query.value(5).toString());
            map.insert(i++, tmp);
        }
    }
    return map;
}
